package com.fyl.core.utils;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

public class JDBCUtil {


    public static List<TableMetaData> getTableInfos(String driver, String url, String user, String pwd) throws Exception {
        try (Connection conn = getConn(driver, url, user, pwd)) {
            DatabaseMetaData metaData = conn.getMetaData();
            int startIndex = url.lastIndexOf("/");
            int endIndex = url.lastIndexOf("?");
            String dbName = url.substring(startIndex + 1, endIndex);
            return getTableInfos(conn, dbName);
        }
    }


    public static TableMetaData getTableInfo(String driver, String url, String user, String pwd, String tableName) throws Exception {
        try (Connection conn = getConn(driver, url, user, pwd)) {
            return getTableInfo(conn, tableName);
        }
    }


    public static List<TableMetaData> getTableInfos(Connection conn, String dbName) throws Exception {
        List<TableMetaData> tableMetaDatas = new ArrayList<>();
        DatabaseMetaData metaData = conn.getMetaData();
        // "%"就是表示*的意思
        ResultSet resultSet = metaData.getTables(dbName, "%", "%", new String[]{"TABLE"});
        while (resultSet.next()) {
            String tableName = resultSet.getString("TABLE_NAME");
            String comment = resultSet.getString("REMARKS");
            TableMetaData tableInfo = getTableInfo(conn, tableName);
            tableInfo.setComment(comment);
            tableMetaDatas.add(tableInfo);
        }
        return tableMetaDatas;
    }

    public static TableMetaData getTableInfo(Connection conn, String tableName) throws Exception {
        TableMetaData tableMetaData = new TableMetaData();
        tableMetaData.setName(tableName);
        ResultSet rs = conn.getMetaData().getColumns(null, getSchema(conn), tableName.toUpperCase(), "%");
        ArrayList<ColumnMetaData> columns = new ArrayList<>();
        while (rs.next()) {
            String columnName = rs.getString("COLUMN_NAME");
            int dataType = rs.getInt("DATA_TYPE");
            String javaType = dbType2JavaType(dataType).getName();
            String dbType = rs.getString("TYPE_NAME");
            int columnSize = rs.getInt("COLUMN_SIZE");
            String nullable = rs.getString("IS_NULLABLE");
            String remarks = rs.getString("REMARKS");
            ColumnMetaData columnMetaData = new ColumnMetaData();
            columnMetaData.setName(columnName);
            columnMetaData.setDbType(dbType);
            columnMetaData.setJavaType(javaType);
            columnMetaData.setSize(columnSize);
            columnMetaData.setIsNullable(nullable);
            columnMetaData.setComment(remarks);
            columns.add(columnMetaData);
            tableMetaData.setColumns(columns);
        }
        return tableMetaData;
    }


    private static Class dbType2JavaType(int dataType) {
        switch (dataType) {
            case Types.TINYINT | Types.SMALLINT | Types.INTEGER:
                return Integer.class;
            case Types.BIGINT:
                return Long.class;
            case Types.FLOAT | Types.DECIMAL | Types.DOUBLE:
                return BigDecimal.class;
            case Types.DATE | Types.TIME | Types.TIMESTAMP:
                return Date.class;
            default:
                return String.class;
        }
    }

    //其他数据库不需要这个方法 oracle和db2需要
    private static String getSchema(Connection conn) throws Exception {
        String schema;
        schema = conn.getMetaData().getUserName();
        if ((schema == null) || (schema.length() == 0)) {
            throw new Exception("ORACLE数据库模式不允许为空");
        }
        return schema.toUpperCase();
    }

    //获取连接
    private static Connection getConn(String driver, String url, String user, String pwd) throws Exception {
        Properties props = new Properties();
//            props.put("remarksReporting", "true");
        props.put("user", user);
        props.put("password", pwd);
        Class.forName(driver);
        return DriverManager.getConnection(url, props);
    }


    public static class TableMetaData {
        private String name;
        private String comment;
        private List<ColumnMetaData> columns;

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getComment() {
            return comment;
        }

        public void setComment(String comment) {
            this.comment = comment;
        }

        public List<ColumnMetaData> getColumns() {
            return columns;
        }

        public void setColumns(List<ColumnMetaData> columns) {
            this.columns = columns;
        }
    }

    public static class ColumnMetaData {
        private String name;
        private String dbType;
        private String javaType;
        private Integer size;
        private String isNullable;
        private String comment;

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getDbType() {
            return dbType;
        }

        public void setDbType(String dbType) {
            this.dbType = dbType;
        }

        public String getJavaType() {
            return javaType;
        }

        public void setJavaType(String javaType) {
            this.javaType = javaType;
        }

        public Integer getSize() {
            return size;
        }

        public void setSize(Integer size) {
            this.size = size;
        }

        public String getIsNullable() {
            return isNullable;
        }

        public void setIsNullable(String isNullable) {
            this.isNullable = isNullable;
        }

        public String getComment() {
            return comment;
        }

        public void setComment(String comment) {
            this.comment = comment;
        }
    }
}
